﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class ChiTietPhieuThueDAO: AbtractDAO
    {     
        public static void themChiTietPhieuThue(long maKhachHang, long maPhieuThue)
        {
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "INSERT INTO CHITIETPHIEUTHUE(MaKhachHang,MaPhieuThue) VALUES(@MaKhachHang,@MaPhieuThue)";

                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo;

                thamSo = new OleDbParameter("@MaKhachHang", OleDbType.Integer);
                thamSo.Value = maKhachHang;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaPhieuThue", OleDbType.Integer);
                thamSo.Value = maPhieuThue;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
        }
        public static long layMaCTPTTheoMaKHvaMaPhieuThue(string maKH,long maphieuthue)
        {
            OleDbConnection ketNoi = null;
            long maHoaDon=0;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "SELECT MaChiTietPhieuThue FROM CHITIETPHIEUTHUE WHERE MaKhachHang=@MaKH and MaPhieuThue = @MaPT";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo2 = new OleDbParameter("@MaKH", OleDbType.Char);
                thamSo2.Value = maKH;

                lenh.Parameters.Add(thamSo2);

                thamSo2 = new OleDbParameter("@MaPT", OleDbType.Integer);
                thamSo2.Value = maphieuthue;
                lenh.Parameters.Add(thamSo2);

                OleDbDataReader boLoc = lenh.ExecuteReader();

                if (boLoc.Read())
                {
                    maHoaDon = boLoc.GetInt32(0);

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return maHoaDon;
        }
        public static List<ChiTietPhieuThueDTO> layDSCTPTtheoDSMaHD(List<HoaDonDTO> dsHD)
        {
            OleDbConnection ketNoi = null;
            List<ChiTietPhieuThueDTO> dsPhieuThue = new List<ChiTietPhieuThueDTO>();
            
                try
                {
                    ketNoi = KetNoi();
                    for (int i = 0; i < dsHD.Count; i++)
                    {
                        string chuoiLenh = "SELECT CTPT.MaChiTietPhieuThue,PT.MaPhong,LP.TenLoaiPhong FROM CHITIETPHIEUTHUE CTPT, CHITIETHOADON CTHD,PHIEUTHUE PT,PHONG P,LOAIPHONG LP WHERE P.MaPhong = PT.MaPhong and P.MaLoaiPhong = LP.MaLoaiPhong and CTPT.MaChiTietPhieuThue = CTHD.MaChiTietPhieuThue and PT.MaPhieuThue = CTPT.MaPhieuThue and CTHD.MaHoaDon = @MaHoaDon";
                        OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);
                        OleDbParameter thamSo2 = new OleDbParameter("@MaHoaDon", OleDbType.Integer);
                        thamSo2.Value = dsHD[i].MaHoaDon;
                        lenh.Parameters.Add(thamSo2);
                        OleDbDataReader boLoc = lenh.ExecuteReader();

                        while (boLoc.Read())
                        {
                            ChiTietPhieuThueDTO CTPhieuThue = new ChiTietPhieuThueDTO();
                            CTPhieuThue.MaChiTietPhieuThue = boLoc.GetInt32(0);
                            PhieuThueDTO PhieuThue = new PhieuThueDTO();
                            if (!boLoc.IsDBNull(1))
                            {
                                PhieuThue.Phong.MaPhong = boLoc.GetInt32(1);
                            }
                                                      
                            if (!boLoc.IsDBNull(2))
                            {
                                PhieuThue.Phong.LoaiPhong.TenLoaiPhong = boLoc.GetString(2);
                            }

                            CTPhieuThue.PhieuThue = PhieuThue;

                            dsPhieuThue.Add(CTPhieuThue);
                        }
                    }
                }
                catch (Exception ex)
                {
                    dsPhieuThue = new List<ChiTietPhieuThueDTO>();
                    throw ex;
                }
                finally
                {
                    if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                        ketNoi.Close();
                }
            
            return dsPhieuThue;
        }
    }
}
